package com.xuyang.utils;

import java.awt.print.Book;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.xuyang.server.BookBean;
import com.xy.bean.BorrowBook;
//demo 第6次
public class SqlHelper {
	private final String str = "com.mysql.jdbc.Driver";
	private final String url = "jdbc:mysql://localhost:3306/company?characterEncoding=utf-8";
	private final String userName = "manager";
	private final String userPwd = "123456";
	private Connection conn = null;
	private PreparedStatement ps = null;
	private ResultSet resultSet = null;
	protected SqlHelper() {
		try {
			Class.forName(str);
			conn = DriverManager.getConnection(url, userName, userPwd);
		} catch (SQLException e) {
			System.err.print("数据库连接异常");
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			System.err.print("数据库文件未找到");
			e.printStackTrace();
		}
	}
	//执行增删改
	public boolean update(String sql,Object[] data){
		if (conn!=null) {
			try {
				ps=conn.prepareStatement(sql);
				for (int i=0;i<data.length;i++) {
					ps.setObject((i+1), data[i]);
				}
				ps.execute();
				return true;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}finally{
				closeAll();
			}
		}else {
			return false;
		}
	}
	//查询这本书是否
	public boolean isBorrow(String sql,String id){
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, id);
			resultSet=ps.executeQuery();
			//如果查询出来的结果不为空
			if(resultSet.next()){
				return true;
			}else {
				return false;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll();
		}
		return false;
	}
	
	//执行查询语句,查询书籍
	public ArrayList<BookBean> select(String sql,Object[] data){
		ArrayList<BookBean> books=new ArrayList<BookBean>();
		try {
			ps=conn.prepareStatement(sql);
			for (int i=0;i<data.length;i++) {
				ps.setObject((i+1), data[i]);
			}
			resultSet = ps.executeQuery();
			while(resultSet.next()){
				BookBean bean=new BookBean(resultSet.getString(1), resultSet.getString(2), resultSet.getString(3), resultSet.getString(4),
						resultSet.getString(5), resultSet.getString(6), resultSet.getString(7), resultSet.getInt(8), 
						resultSet.getString(9));
				books.add(bean);
			}
			return books;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll();
		}
		return null;
	}
	//执行查询语句,查询借阅
		public ArrayList<BorrowBook> selectBorrow(int flag,String uname){
			ArrayList<BorrowBook> books=new ArrayList<BorrowBook>();
			String sql=null;
			if (flag==1) {
				//查出当前用户当前借出的书籍ID 和借阅时间
				sql="select bookId,browTime from bookborrow where userName = ? and returned=0";
			}else if(flag==2){
				//历史借阅
				sql="select bookId,browTime from bookborrow where userName = ? and returned=1";
			}
			try {
				ps=conn.prepareStatement(sql);
				ps.setString(1, uname);
				resultSet = ps.executeQuery();
				while(resultSet.next()){
					BorrowBook bean=new BorrowBook(resultSet.getString(1), resultSet.getString(2));
					books.add(bean);
				}
				for (BorrowBook borrowBook : books) {
					sql = "select name from bookstacks where id = "+borrowBook.getBookName();
					ps=conn.prepareStatement(sql);
					resultSet=ps.executeQuery();
					resultSet.next();
					borrowBook.setBookName(resultSet.getString(1));
				}
				return books;
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				closeAll();
			}
			return null;
		}
	//用户是否正确 如果不正确则返回-1  如果正确则返回该用户的权限等级
	public int loginSuccess(String username,String password){
		try {
			
			ps=conn.prepareStatement("select level from users where username=? and password=?");
			ps.setString(1, username);
			ps.setString(2, password);
			resultSet=ps.executeQuery();
			//如果查询结果不为空
			if (resultSet.next()) {
				return resultSet.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}
	
	private void closeAll(){
		if (resultSet!=null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (ps!=null) {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
